package com.hh.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hh.jdbc.JDBCDemo;

public class ScheduleDAO {
	public JSONArray getDoctorList(String zxks){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
            String nowDate = df.format(new Date());
            nowDate="2019-06-05";
            String startDate = nowDate+" 00:00:00";
            String endDate = nowDate+" 23:59:59";
            String sql = "select distinct wh_kfys.xm,zxry from PD_XMYY,wh_kfys "
            		+ " where  zxry=ygdm "
            		+ " and jhrq>='"+startDate+"' "
//            		+ " and jhrq<= '"+endDate+"' "
            		+ " and YYBZ=1 ";
            if(zxks!=null&&!zxks.equals("")){
            		sql+= " and zxks='"+zxks+"'";
            }
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject o = new JSONObject();
            	o.put("xm", res.getString("xm"));
            	o.put("zxry", res.getString("zxry"));
            	array.add(o);
            }
            
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	
	public String getPatient(String zxks,String kssj,String jssj,String zxry){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
            String nowDate = df.format(new Date());
            String startDate = nowDate+" 00:00:00";
            String endDate = nowDate+" 23:59:59";
            String sql = "select distinct xm from PD_XMYY "
            		+ " where  YYBZ=1 "
            		+ " and jhrq>='"+startDate+"' "
            		+ " and jhrq<= '"+endDate+"' "
            		+ " and kssj='"+kssj+"' "
    				+ " and jssj='"+jssj+"' "
					+ " and zxry='"+zxry+"' " ;
            if(zxks!=null&&!zxks.equals("")){
            		sql+= " and zxks='"+zxks+"'";
            }
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            if(res.next()){
            	return res.getString("xm");
            }
            
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return "";
	}
	
	public JSONArray getSchedulesByNurseWeek(String ygdm,int day){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = " SELECT * FROM HL_HGPB WHERE YGDM='"+ygdm+"'"
            		+ "  AND PBRQ between '"+getWeekStart(day)+"' and '"+getWeekEnd(day)+"'";
           
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("ygdm", res.getString("YGDM"));
            	object.put("pbrq", res.getString("PBRQ"));
            	object.put("ksrq", res.getString("KSRQ"));
            	object.put("jsrq", res.getString("JSRQ"));
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	public JSONArray getSchedulesByNurseAllWeek(String ygdm){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray array = new JSONArray();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = " SELECT * FROM HL_HGPB WHERE YGDM='"+ygdm+"'"
            		+ "  AND PBRQ between '"+getWeekStart(-7)+"' and '"+getWeekEnd(7)+"'";
           
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            while(res.next()){
            	JSONObject object = new JSONObject();
            	object.put("ygdm", res.getString("YGDM"));
            	object.put("pbrq", res.getString("PBRQ"));
            	object.put("ksrq", res.getString("KSRQ"));
            	object.put("jsrq", res.getString("JSRQ"));
            	array.add(object);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return array;
	}
	public boolean insertSchedule(String ygdm,String pbrq,String ksrq,String jsrq){
		Connection con = null;
        PreparedStatement statement = null;
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "insert into HL_HGPB (YGDM,PBRQ,KSRQ,JSRQ) values(?,?,?,?)" ;
            statement = con.prepareStatement(sql);
            statement.setString(1, ygdm);
            statement.setString(2, pbrq);
            statement.setString(3, ksrq);
            statement.setString(4, jsrq);
            int i  = statement.executeUpdate();
            if(i>0){
            	return true;
            }
           
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return false;
	}
	
	public boolean updateSchedule(String ygdm,String pbrq,String ksrq,String jsrq){
		Connection con = null;
        PreparedStatement statement = null;
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "update HL_HGPB set KSRQ=?,JSRQ=? where YGDM=? and PBRQ=? " ;
            statement = con.prepareStatement(sql);
            statement.setString(1, ksrq);
            statement.setString(2, jsrq);
            statement.setString(3, ygdm);
            statement.setString(4, pbrq);
            int i  = statement.executeUpdate();
            if(i>0){
            	return true;
            }
           
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return false;
	}
	
	public boolean deleteSchedule(String ygdm,String pbrq){
		Connection con = null;
        PreparedStatement statement = null;
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = "delete from  HL_HGPB where YGDM=? and PBRQ=? " ;
            statement = con.prepareStatement(sql);
            statement.setString(1, ygdm);
            statement.setString(2, pbrq);
            int i  = statement.executeUpdate();
            if(i>0){
            	return true;
            }
           
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return false;
	}
	
	public JSONObject getSchedulesByNurse(String ygdm,String pbrq){
		Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONObject object = new JSONObject();
        try {
            Class.forName(JDBCDemo.cfn);
            con = DriverManager.getConnection(JDBCDemo.url,JDBCDemo.user,JDBCDemo.password);
            String sql = " SELECT * FROM HL_HGPB WHERE YGDM='"+ygdm+"' AND PBRQ ='"+pbrq+"'";
           
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            if(res.next()){
            	object.put("ygdm", res.getString("YGDM"));
            	object.put("pbrq", res.getString("PBRQ"));
            	object.put("ksrq", res.getString("KSRQ"));
            	object.put("jsrq", res.getString("JSRQ"));
            }
            sql = " SELECT * FROM HL_SXBQD WHERE YGDM='"+ygdm+"'  AND QDRQ ='"+pbrq+"'";
            statement = con.prepareStatement(sql);
            res = statement.executeQuery();
            if(res.next()){
            	object.put("sbsj_bz", res.getString("SBSJ_BZ"));
            	object.put("sbsj_sj", res.getString("SBSJ_SJ"));
            	object.put("xbsj_bz", res.getString("XBSJ_BZ"));
            	object.put("xbsj_sj", res.getString("XBSJ_SJ"));
            }
            
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            try {
                if(res != null) res.close();
                if(statement != null) statement.close();
                if(con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return object;
	}
	
	public String getWeekStart(int i){
		Calendar cal=Calendar.getInstance();
		cal.add(Calendar.WEEK_OF_MONTH, 0);
		cal.set(Calendar.DAY_OF_WEEK, 2);
		cal.set(Calendar.DATE, cal.get(Calendar.DATE) + i);
		Date time=cal.getTime();
		return new SimpleDateFormat("yyyy-MM-dd").format(time);
	}
	
	public String getWeekEnd(int i){
		Calendar cal=Calendar.getInstance();
		cal.set(Calendar.DAY_OF_WEEK, cal.getActualMaximum(Calendar.DAY_OF_WEEK));
		cal.add(Calendar.DAY_OF_WEEK, 1);
		cal.set(Calendar.DATE, cal.get(Calendar.DATE) + i);
		Date time=cal.getTime();
		return new SimpleDateFormat("yyyy-MM-dd").format(time);
	}
	
	
}
